# @Project: diablo
# @File: agreement_by_goods_code
# @Time: 2023/12/14 14:18
# @User: Jolly
# @Email: jolly@007vin.com

"""
给指定商品编码定价
"""

import requests
import datetime
# 小汽配2.0初始化商品定价
# 定价来源：客户提供的excel
from copy import deepcopy

import time
import threading
from queue import Queue

from io import BytesIO
from openpyxl import load_workbook
from pymongo import MongoClient
from pymongo.errors import ConnectionFailure

lock = threading.Lock()


class InitAgreementPrice:

    def __init__(self, merchant_id, agreement_excel_id, file_path):

        self.merchant_id = merchant_id
        self.agreement_excel_id = agreement_excel_id
        self.file_path = f"./{file_path}.xlsx"

        self.stock_config = {
            "3": {  # 总仓
                'erp_type': "ChangYuan",
                'erp_uid': "T0000002_4TH0WP2OQ_T",
                'app_key': "49914AB6-87D8-4AD7-A859-1DC970EAA8AD",
            },
            "1390f84351fe464b90dcdbf3a164ff24": {  # 一线品牌
                'erp_type': "ChangYuan",
                'erp_uid': "T0000002_4TH0WP2OQ_Z1",
                'app_key': "49914AB6-87D8-4AD7-A859-1DC970EAA8AD",
            }
        }

        start_time = time.time()

        # 初始化数据库连接
        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 执行任务
        self.task()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=250,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    # 判断定价协议表是否已有对应数据，有的话就删除
    def check_agreement_exist(self, goods_code, price):

        check = False

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_goods_price"]

                filter_dict = {
                    "merchant_id": self.merchant_id,
                    "agreement_excel_id": self.agreement_excel_id,
                    "code": goods_code
                }
                docs_count = conn.count_documents(filter_dict)

                if docs_count > 0:
                    # 删除已有的
                    # conn.delete_many(filter_dict)

                    docs = conn.find(filter_dict, {'agreement_price': 1, 'price_user': 1})

                    for doc in docs:
                        agreement_price = doc.get("agreement_price", 0)
                        price_user = doc.get("price_user", "")
                        if float(agreement_price) == price:
                            if price_user != "价格更变定价":
                                check = True
                                break

                    if not check:
                        # 说明定价不是最新的，需要删除
                        conn.delete_many(filter_dict)

                    # check = True

        except Exception as e:
            print("获取协议定价存在检测失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

        return check

    # 写入数据
    def write_data(self, *args):

        oe = args[0]
        origin_quality = args[1]
        price = args[2]

        # oe去除空格
        oe = oe.strip()
        oe = oe.replace(' ', '')

        # 如果存在斜杠或者等于号，则取前半部分
        if "//" in oe:
            oe = oe.split("/")[0]

        if "=" in oe:
            oe = oe.split("=")[0]

        if "-" in oe:
            oe = oe.split("-")[0]

        origin_oe = deepcopy(oe)

        # 非数字字符串则返回
        if isinstance(price, str):
            if not price.isdigit():
                with open("./goods_code_is_not_match.txt", "a") as file:
                    file.write(oe + " - " + origin_quality + "\n")
                return

        # 获得goods_code
        # goods_code = self.zc_seed.get(oe + "-" + origin_quality)
        # if not goods_code:
        #     with open("./not_hit.txt", "a") as file:
        #         file.write(oe + " - " + origin_quality + "\n")
        #     return

        goods_code = ""

        # 因为oe可能会被去除0，所以补齐四次，四次都没有则记录
        for i in range(0, 5):

            # 获得goods_code
            err, res = self.get_goods_code_by_erp(oe, origin_quality)
            if err:
                with open("./goods_code_err.txt", "a") as file:
                    file.write(origin_oe + " - " + origin_quality + "\n")
                return

            data = res.get('data')

            if len(data['stocks']) == 0:

                if i != 4:
                    oe = "0" + oe
                    continue
                else:
                    with open("./goods_code_is_not_match.txt", "a") as file:
                        file.write(origin_oe + " - " + origin_quality + "\n")
                    return

                # with open("./not_hit.txt", "a") as file:
                #     file.write(oe + " - " + origin_quality + "\n")
                #
                # with open("./goods_code_is_not_match.txt", "a") as file:
                #     file.write(oe + " - " + origin_quality + "\n")
                # return

            for item in data['stocks']:
                origin_oe = item.get("opid")
                oem = item.get("oem")

                origin_oe = origin_oe.strip()
                origin_oe = origin_oe.replace(' ', '')

                oem = oem.strip()
                oem = oem.replace(' ', '')

                if origin_oe != oe:
                    if oem != oe:
                        continue

                goods_code = item.get("goods_code")

            if goods_code == "":

                if i != 4:
                    oe = "0" + oe
                    continue
                else:
                    with open("./goods_code_is_not_match.txt", "a") as file:
                        file.write(origin_oe + " - " + origin_quality + "\n")
                    return
            else:
                break

        # # 获得goods_code
        # err, res = self.get_goods_code_by_erp(oe, origin_quality)
        # if err:
        #     with open("./goods_code_err.txt", "a") as file:
        #         file.write(oe + " - " + origin_quality + "\n")
        #     return
        #
        # data = res.get('data')
        #
        # if len(data['stocks']) == 0:
        #     with open("./not_hit.txt", "a") as file:
        #         file.write(oe + " - " + origin_quality + "\n")
        #
        #     with open("./goods_code_is_not_match.txt", "a") as file:
        #         file.write(oe + " - " + origin_quality + "\n")
        #     return
        #
        # goods_code = ""
        # for item in data['stocks']:
        #     origin_oe = item.get("opid")
        #     oem = item.get("oem")
        #
        #     origin_oe = origin_oe.strip()
        #     origin_oe = origin_oe.replace(' ', '')
        #
        #     oem = oem.strip()
        #     oem = oem.replace(' ', '')
        #
        #     if origin_oe != oe:
        #         if oem != oe:
        #             continue
        #
        #     goods_code = item.get("goods_code")
        #
        # if goods_code == "":
        #     with open("./goods_code_is_not_match.txt", "a") as file:
        #         file.write(oe + " - " + origin_quality + "\n")
        #     return

        # 获取库存价格
        stock_doc = self.get_stock_price(goods_code)
        if not stock_doc:
            print(f"库存价格为空--------{goods_code}")
            stock_price = 0
        else:
            stock_price = stock_doc.get("stock_price")

        # 处理数字小数点
        if '.' in str(price):
            integer_part, decimal_part = str(price).split('.')
            decimal_part = decimal_part[:2]
            price = float(f'{integer_part}.{decimal_part}')
        else:
            price = float(price)

        # 返回的库存identify，再去查询agreement_price中，是否存在数据，存在则删除
        check = self.check_agreement_exist(goods_code, price)
        if not check:

            # 如果定价小于库存价，则以定价金额直接定价
            if float(price) <= stock_price or stock_price == 0:
                price_type = "customize"
            else:
                price_type = "percent_add"

            # 计算涨幅
            up_rate = self.culculate_price_rate(stock_price, price, price_type)
            # 拼接描述
            price_desc = self.public_build_price_desc(stock_price, price_type, up_rate)

            agreement_dict = {
                "agreement_excel_id": self.agreement_excel_id,
                "merchant_id": self.merchant_id,
                "code": goods_code,
                "agreement_price": float(price),
                "price_type": price_type,
                "price_value": up_rate,
                "price_desc": price_desc,
                "update_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                "user_id": "0000000000000000003",  # 当前用户id
                "price_user": "库存价变更",  # 当前用户名
                "price_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            }

            # 获取2.0MongoDB连接
            connection = self.diablo_conn_2_0

            try:
                if connection is not None:
                    # 执行数据库操作
                    db = connection["inventory_data"]
                    conn = db["agreement_goods_price"]
                    conn.insert_one(dict(agreement_dict))

            except Exception as e:
                print("写入2.0MongoDB数据库单据失败：", e)
            finally:
                # 将连接放回连接池
                connection.close()

    # 获取excel数据，生成器返回
    def get_excel_data(self):

        with open(self.file_path, "rb") as file:
            content = file.read()

        # 将内容转换为BytesIO对象
        bytesIO = BytesIO(content)

        # 加载工作簿
        book = load_workbook(bytesIO, read_only=True)

        # 获取活动工作表
        sheet = book.active

        previous = next(sheet.iter_rows(), None)
        for row in sheet.iter_rows():
            yield previous, False
            previous = row
        yield previous, True

    def get_all_excel_data(self):
        """
        获取全部excel
        """

        with open(self.file_path, "rb") as file:
            content = file.read()

        # 将内容转换为BytesIO对象
        bytesIO = BytesIO(content)

        # 加载工作簿
        book = load_workbook(bytesIO, read_only=True)

        # 获取活动工作表
        sheet = book.active

        return sheet.iter_rows()

    def get_zongcang_goods_code_seed(self):
        """
        获取总仓库存excel，并清洗商品编码字典
        """
        with open("./hongdezongcang.xlsx", "rb") as file:
            content = file.read()

        # 将内容转换为BytesIO对象
        bytesIO = BytesIO(content)

        # 加载工作簿
        book = load_workbook(bytesIO, read_only=True)

        # 获取活动工作表
        sheet = book.active

        zc_seed = {}

        for row in sheet.iter_rows():
            goods_code = str(row[0].value)
            oe = str(row[1].value)
            quality = str(row[2].value)

            # oe去除空格
            oe = oe.strip()
            oe = oe.replace(' ', '')

            zc_seed[oe + "-" + quality] = goods_code

        self.zc_seed = zc_seed

        return

    def get_goods_code_by_erp(self, keyword, quality):

        url = 'http://192.168.191.245:11180/v2/openapi/goods/erpparts/list'

        # 获取库存配置
        config_dict = self.stock_config.get(self.merchant_id)

        kwargs = {
            'page': "1",
            'size': "10",
            'keyword': keyword,
            'quality': quality
        }

        kwargs.update(config_dict)

        res = requests.post(url=url, json=kwargs).json()
        if res.get('code') != 1:
            return True, res
        return False, res

    def get_stock_price(self, code):

        # show_fields.update({
        #     'code': 1,
        #     '_id': 0
        # })
        # filter_dict = {
        #     'merchant_id': merchant_id,
        # }
        # if goods_codes:
        #     filter_dict.update({
        #         'code': {"$in": goods_codes}
        #     })
        # if no_stock_price:
        #     filter_dict.update({
        #         'stock_price': {"$eq": None},
        #     })
        # result = self.db.goods.find(filter_dict, show_fields)

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:

                # 执行数据库操作
                db = connection["ddll"]
                conn = db["goods"]

                filter_dict = {
                    "merchant_id": self.merchant_id,
                    "code": code
                }

                docs = conn.find(filter_dict, {"stock_price": 1})

                docs_count = conn.count_documents(filter_dict)

                if docs_count > 0:

                    for doc in docs:
                        if not doc.get("stock_price"):
                            continue
                        else:
                            return doc

                    # {
                    #     "_id": ObjectId("6551ab6a35b4d63c7f20b7cd"),
                    #     "agreement_excel_id": NumberInt(33),
                    #     "identify": "08a75bd88aba05ff39e599a68e01d63c",
                    #     "agreement_price": NumberInt(205),
                    #     "price_type": "customize",
                    #     "price_value": NumberInt(205),
                    #     "price_desc": "¥ = 205",
                    #     "update_time": "2023-11-13 12:51:54",
                    #     "user_id": "0000000000000000003",
                    #     "price_user": "系统人工控价",
                    #     "price_time": "2023-11-13"
                    # }

                else:
                    return {}

        except Exception as e:
            print("获取原库存定价失败2 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    def culculate_price_rate(self, _sale_price, _agreement_price, _price_type):
        """
        计算原定价相比现有商品库存价上涨比例，并返回

        计算新的加价比例
        _sale_price: 库存价
        _agreement_price: 新协议价
        _price_type: 加价类型
        """
        if isinstance(_agreement_price, str):
            _agreement_price = float(_agreement_price)
        if isinstance(_sale_price, str):
            _sale_price = float(_sale_price)
        if _sale_price <= 0:
            return 0
        if _price_type == 'percent_sub':  # 降价
            _price_value = round(1 - _agreement_price / _sale_price, 4)
        elif _price_type == 'percent_add':  # 加价
            _price_value = round(_agreement_price / _sale_price - 1, 4)
        elif _price_type == 'selected_add':  # 定价 加价
            _price_value = round(_agreement_price - _sale_price, 4)
        elif _price_type == 'selected_sub':  # 定价 降价
            _price_value = round(_sale_price - _agreement_price, 4)
        elif _price_type == 'customize':  # 固定 价格
            _price_value = _agreement_price
        else:
            _price_value = 0

        return _price_value

    def public_build_price_desc(self, sale_price, price_type, price_value):
        """拼接加价规则描述"""
        if isinstance(sale_price, str):
            sale_price = float(sale_price)
        if isinstance(price_value, str):
            price_value = float(price_value)
        if price_type == 'percent_sub':  # 降价
            return f'¥ = {sale_price}*{round((1 - price_value) * 100, 2)}%'
        elif price_type == 'percent_add':  # 加价
            return f'¥ = {sale_price}*{round((1 + price_value) * 100, 2)}%'
        elif price_type == 'selected_add':  # 定价 加价
            return f'¥ = {sale_price}+{price_value}'
        elif price_type == 'selected_sub':  # 定价 降价
            return f'¥ = {sale_price}-{price_value}'
        elif price_type == 'customize':  # 固定 价格
            return f'¥ = {price_value}'
        return ''

    def fixed_same_quality(self, all_data):
        """
        因为存在多仓库，oe和品质相同，价格不同的数据，需要处理只保留最大的
        """

        data_list = []

        for row in all_data:
            oe = str(row[0].value)
            origin_quality = row[1].value
            price = row[2].value

            # oe去除空格
            oe = oe.strip()
            oe = oe.replace(' ', '')

            data_list.append({
                "oe": oe,
                "origin_quality": origin_quality,
                "price": price
            })

        # 获取最大的
        result = {}

        for item in data_list:
            key = (item["oe"], item["origin_quality"])
            if key not in result or item["price"] > result[key]["price"]:
                result[key] = item

        final_result = list(result.values())

        # print(final_result)

        return final_result

    # 任务执行
    def task(self):
        q = Queue(maxsize=200)  # 设定最大队列数和线程数

        count = 1

        # self.get_zongcang_goods_code_seed()

        all_data = self.get_all_excel_data()

        # 因为存在多仓库，oe和品质相同，价格不同的数据，需要处理只保留最大的
        fixed_data = self.fixed_same_quality(all_data)

        with open("./len.txt", "a") as file:
            file.write("待处理长度, " + str(len(fixed_data)) + "\n")

        for row in fixed_data:
            oe = row.get("oe")
            origin_quality = row.get("origin_quality")
            price = row.get("price")

            t = threading.Thread(target=self.write_data, args=(oe, origin_quality, price))
            q.put(t)
            if q.full() == True:
                thread_list = []
                while q.empty() == False:
                    t = q.get()
                    thread_list.append(t)
                    t.start()
                for t in thread_list:
                    t.join()

            print("当前写入数：", count, " 零件为：", oe, ", 品质为：", origin_quality, ", 价格为：", price)

            count += 1

        # 处理残余队列数据
        thread_list = []
        while q.empty() == False:
            t = q.get()
            thread_list.append(t)
            t.start()
        for t in thread_list:
            t.join()


if __name__ == "__main__":
    # file_list = ["hongde2023121401", "hongde2023121802]
    #
    # for file_path in file_list:
    #     print(f"当前刷写的是-------------------{file_path}----------------------------")
    #     InitAgreementPrice("3", 33, file_path)
    #     InitAgreementPrice("1390f84351fe464b90dcdbf3a164ff24", 33, file_path)

    # 总仓
    # InitAgreementPrice("3", 315, "hongde2024032101")
    # 一线品牌
    InitAgreementPrice("1390f84351fe464b90dcdbf3a164ff24", 315, "hongde2024032101")